#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库操作管理类
负责与SQLite数据库进行交互，提供todo_list表的CRUD操作
"""
import os
import sqlite3
from datetime import datetime
from readlater.utils.logger import get_logger

class DatabaseManager:
    STATUS_LINK_EXTRACTED = 'link_extracted'
    STATUS_CONTENT_DOWNLOADED = 'content_downloaded'
    STATUS_PROCESSED = 'processed'
    STATUS_SYNCED = 'synced'
    STATUS_ERROR = 'error'



    """数据库操作管理类"""

    def __init__(self, db_path: str):

        """
        初始化数据库连接

        Args:
            db_path: 数据库文件路径，如果为None则使用默认路径
        """
        # 初始化日志记录器
        self.logger = get_logger(__name__)
        self.db_path = db_path
        self.conn = None
        self.cursor = None
        self.logger.info(f"数据库路径: {self.db_path}")

    def connect(self):
        """
        连接到数据库

        Returns:
            bool: 连接成功返回True，失败返回False
        """
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.cursor = self.conn.cursor()
            self.logger.info("数据库连接成功")
            return True
        except sqlite3.Error as e:
            self.logger.error(f"数据库连接失败: {str(e)}")
            return False

    def close(self):
        """
        关闭数据库连接
        """
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
            self.logger.info("数据库连接已关闭")

    def begin_transaction(self):
        """
        开始事务
        """
        try:
            self.cursor.execute("BEGIN TRANSACTION;")
            self.logger.debug("事务已开始")
        except sqlite3.Error as e:
            self.logger.error(f"开始事务失败: {str(e)}")
            raise

    def commit(self):
        """
        提交事务
        """
        try:
            self.conn.commit()
            self.logger.debug("事务已提交")
        except sqlite3.Error as e:
            self.logger.error(f"提交事务失败: {str(e)}")
            raise

    def rollback(self):
        """
        回滚事务
        """
        try:
            self.conn.rollback()
            self.logger.debug("事务已回滚")
        except sqlite3.Error as e:
            self.logger.error(f"回滚事务失败: {str(e)}")
            raise

    def create_table(self):
        """
        创建数据表
        根据sql/create_tables.sql中的表结构定义
        """
        try:
            # 读取sql文件内容
            sql_path = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'sql', 'create_tables.sql'))
            with open(sql_path, 'r', encoding='utf-8') as f:
                sql_script = f.read()

            # 执行SQL脚本
            self.cursor.executescript(sql_script)
            self.conn.commit()
            self.logger.info("数据表创建成功")
            return True
        except sqlite3.Error as e:
            self.logger.error(f"创建数据表失败: {str(e)}")
            self.conn.rollback()
            return False

    def insert_article(self, article_id: str, url: str, status: str, title: str = None, file_name: str = None, summary: str = None, tags: str = None, failed_reason: str = None):

        """
        插入新的链接记录

        Args:
            link_id: 链接ID (MD5(url))
            url: 链接地址
            status: 处理状态
            title: 文章标题
            file_name: 文件名
            summary: 摘要
            tags: 标签
            failed_reason: 失败原因

        Returns:
            bool: 插入成功返回True，失败返回False
        """
        try:
            sql = """
                INSERT INTO todo_list (id, url, title, status, file_name, summary, tags, failed_reason, datetime)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """

            params = (
                article_id,
                url,
                title,
                status,
                file_name,
                summary,
                tags,
                failed_reason,
                datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

            self.cursor.execute(sql, params)
            self.logger.info(f"成功插入链接: {url}")
            return True
        except sqlite3.Error as e:
            self.logger.error(f"插入链接失败: {str(e)}, URL: {url}")
            return False

    def update_article(self, article_id, **kwargs):
        """
        更新链接记录

        Args:
            link_id: 链接ID
            **kwargs: 要更新的字段和值

        Returns:
            bool: 更新成功返回True，失败返回False
        """
        try:
            self.begin_transaction()

            # 构建更新字段
            update_fields = []
            params = []

            for field, value in kwargs.items():
                if field in ['title', 'status', 'file_name', 'summary', 'tags', 'failed_reason', 'feishu_record_id']:
                    update_fields.append(f"{field} = ?")
                    params.append(value)

            # 添加更新时间
            update_fields.append("datetime = ?")
            params.append(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))

            # 添加ID参数
            params.append(article_id)

            # 构建SQL
            sql = f"UPDATE todo_list SET {', '.join(update_fields)} WHERE id = ?"

            self.cursor.execute(sql, params)
            self.commit()
            self.logger.info(f"成功更新链接: {article_id}")
            return True
        except sqlite3.Error as e:
            self.logger.error(f"更新链接失败: {str(e)}, ID: {article_id}")
            self.rollback()
            return False

    def delete_link(self, link_id):
        """
        删除链接记录

        Args:
            link_id: 链接ID

        Returns:
            bool: 删除成功返回True，失败返回False
        """
        try:
            self.begin_transaction()

            sql = "DELETE FROM todo_list WHERE id = ?"
            self.cursor.execute(sql, (link_id,))
            self.commit()
            self.logger.info(f"成功删除链接: {link_id}")
            return True
        except sqlite3.Error as e:
            self.logger.error(f"删除链接失败: {str(e)}, ID: {link_id}")
            self.rollback()
            return False

    def get_article_by_id(self, article_id):
        """
        根据ID获取链接记录

        Args:
            link_id: 链接ID

        Returns:
            dict: 链接记录信息，如果不存在返回None
        """
        try:
            sql = "SELECT * FROM todo_list WHERE id = ?"
            self.cursor.execute(sql, (article_id,))
            row = self.cursor.fetchone()

            if row is None:
                self.logger.info(f"未找到链接: {article_id}")
                return None

            # 转换为字典
            columns = [desc[0] for desc in self.cursor.description]
            link_info = dict(zip(columns, row))
            self.logger.info(f"成功获取链接: {article_id}")
            return link_info
        except sqlite3.Error as e:
            self.logger.error(f"获取链接失败: {str(e)}, ID: {article_id}")
            return None

    def get_articles_by_status(self, status):
        """
        根据状态获取链接记录

        Args:
            status: 处理状态

        Returns:
            list: 链接记录列表
        """
        try:
            sql = "SELECT * FROM todo_list WHERE status = ? ORDER BY datetime asc"
            self.cursor.execute(sql, (status,))
            rows = self.cursor.fetchall()

            # 转换为字典列表
            columns = [desc[0] for desc in self.cursor.description]
            articles = [dict(zip(columns, row)) for row in rows]
            self.logger.info(f"成功获取状态为 {status} 的链接，共 {len(articles)} 条")
            return articles
        except sqlite3.Error as e:
            self.logger.error(f"获取链接失败: {str(e)}, 状态: {status}")
            return []

    def get_all_links(self):
        """
        获取所有链接记录

        Returns:
            list: 链接记录列表
        """
        try:
            sql = "SELECT * FROM todo_list ORDER BY datetime DESC"
            self.cursor.execute(sql, ())
            rows = self.cursor.fetchall()

            # 转换为字典列表
            columns = [desc[0] for desc in self.cursor.description]
            links = [dict(zip(columns, row)) for row in rows]
            self.logger.info(f"成功获取所有链接，共 {len(links)} 条")
            return links
        except sqlite3.Error as e:
            self.logger.error(f"获取所有链接失败: {str(e)}")
            return []


# 测试代码
if __name__ == '__main__':
    # 初始化数据库管理器
    db_manager = DatabaseManager("./data/read_later.db")


    # 连接数据库
    if db_manager.connect():
        # 创建表
        db_manager.create_table()

        # 测试插入
        import hashlib
        url = "https://example.com"
        link_id = hashlib.md5(url.encode()).hexdigest()
        db_manager.insert_article(
            article_id=link_id,
            url=url,
            title="示例网站",
            status="pending"
        )

        # 测试查询
        link = db_manager.get_article_by_id(link_id)
        print(link)

        # 测试更新
        db_manager.update_article(link_id, status="processing")

        # 测试查询状态
        processing_links = db_manager.get_articles_by_status("processing")
        print(processing_links)

        # 关闭连接
        db_manager.close()
    else:
        print("数据库连接失败")